﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;
using System.Data;

namespace DAO
{
    public class SachDAO
    {
        DataProvider dp;
        public SachDAO()
        {
            dp = new DataProvider();
        }
        public DataTable LayDSSach()
        {
            string sql = "select * from Sach";
            DataTable table = new DataTable();
            table = dp.LayBangDuLieu(sql);
            return table;
        }
        public DataTable LayDSSQL()
        {
            string sql = "select s.masach, s.tensach,s.matheloai, tl.tentheloai, s.tacgia, s.soluongton, s.dongia from Sach s, TheLoai tl where s.matheloai = tl.matheloai";
            DataTable table = new DataTable();
            table = dp.LayBangDuLieu(sql);
            return table;
        }
        public DataTable TimSachTheoTheLoai(int matheloai)
        {
            string sql = "select s.masach, s.tensach,s.matheloai, tl.tentheloai, s.tacgia, s.soluongton, s.dongia from Sach s, TheLoai tl where s.matheloai = tl.matheloai and s.matheloai = " + matheloai + "";
            DataTable table = new DataTable();
            table = dp.LayBangDuLieu(sql);
            return table;
        }
        public bool ThemSach(SachDTO sdto)
        {
            bool flag = true;
            string sql = "insert into Sach(tensach,tacgia,matheloai,dongia,soluongton) ";
            sql += "values('" + sdto.Tensach + "', '" + sdto.Tacgia + "', '" + sdto.Matheloai + "', '" + sdto.Dongia + "',0)";
            if (!dp.ThucHienCauTruyVan(sql)) flag = false;
            return flag;
        }
        public bool XoaSach(int masach)
        {
            bool flag = true;
            string sql = "delete from Sach where masach = " + masach + "";
            if (!dp.ThucHienCauTruyVan(sql)) flag = false;
            return flag;
        }
        public bool CapNhatSach(SachDTO sdto)
        {
            bool flag = true;
            string sql = "update Sach set tensach = '" + sdto.Tensach + "', tacgia = '" + sdto.Tacgia + "', matheloai = '" + sdto.Matheloai + "', ";
            sql += "dongia = '" + sdto.Dongia + "', soluongton = '" + sdto.Soluongton + "' where masach = " + sdto.Masach + "";
            if (!dp.ThucHienCauTruyVan(sql)) flag = false;
            return flag;
        }
        public SachDTO LayMotCuonSach(int masach)
        {
            SachDTO sdto = new SachDTO();
            string sql = "select * from Sach where masach = " + masach + "";
            DataTable table = new DataTable();
            table = dp.LayBangDuLieu(sql);
            DataRow dr = table.Rows[0];

            sdto.Masach = int.Parse(dr[0].ToString());
            sdto.Tensach = dr[1].ToString();
            sdto.Tacgia = dr[2].ToString();
            sdto.Matheloai = int.Parse(dr[3].ToString());
            sdto.Dongia = double.Parse(dr[4].ToString());
            sdto.Soluongton = int.Parse(dr[5].ToString());

            return sdto;
        }

        public DataTable TimKiemSach(string tensach, string tacgia, int matheloai)
        {
            DataTable table = new DataTable();
            string sql = "select s.masach, s.tensach,s.matheloai, tl.tentheloai, s.tacgia, s.soluongton, s.dongia from Sach s, TheLoai tl where s.matheloai = tl.matheloai ";
            if (tensach != "") sql += " and Instr(s.tensach,'" + tensach + "')> 0 ";
            if (tacgia != "") sql += " and Instr(s.tacgia,'" + tacgia + "')> 0 ";
            if (matheloai != 0) sql += " and s.matheloai = " + matheloai + "";
            table = dp.LayBangDuLieu(sql);
            return table;
        }
        public string LayTheLoaiSach(int masach)
        {
            string theloai = "";
            string sql = "select tl.tentheloai from Sach s, TheLoai tl where s.matheloai = tl.matheloai and s.masach = " + masach + "";
            DataTable table = new DataTable();
            table = dp.LayBangDuLieu(sql);
            DataRow dr = table.Rows[0];
            theloai = dr[0].ToString();
            return theloai;
        }
        public bool CapNhatSoLuongSach(int masach, int soluong)
        {
            bool flag = true;
            SachDTO sdto = LayMotCuonSach(masach);
            int sl = sdto.Soluongton;
            int sl1 = sl - soluong;
            string sql = "update Sach set soluongton = " + masach + " where masach = " + masach + "";
            if (!dp.ThucHienCauTruyVan(sql)) flag = false;
            return flag;
        }

    }
}
